﻿CREATE PROCEDURE [dbo].[ListProjectsWithMembership]
@QueryUserID INT, @UserID INT, @StatusID INT=NULL
AS
DECLARE @@QueryUserRoleID int
SELECT @@QueryUserRoleID = Users.RoleID FROM Users WHERE Users.UserID = @QueryUserID

IF @@QueryUserRoleID = 1 OR @QueryUserID = @UserID
  BEGIN
	SELECT 	Projects.ProjectID,
		Name, 
		Description, 
		ManagerUserID, 
		EstCompletionDate, 
		EstDuration,
		ProjectTypeID,
		COALESCE(StatusID, 1) AS StatusID
	FROM Projects 
	INNER JOIN ProjectMembers ON ProjectMembers.ProjectID = Projects.ProjectID
	WHERE UserID = @UserID
	AND StatusID = COALESCE(@StatusID, StatusID)
	ORDER BY Name
  END  
ELSE IF @@QueryUserRoleID = 2
  BEGIN
	SELECT 	Projects.ProjectID,
		Name, 
		Description, 
		ManagerUserID, 
		EstCompletionDate, 
		EstDuration,
		ProjectTypeID,
		COALESCE(StatusID, 1) AS StatusID
	FROM Projects 
	INNER JOIN ProjectMembers ON ProjectMembers.ProjectID = Projects.ProjectID
	WHERE UserID = @UserID AND ManagerUserID = @QueryUserID
	AND StatusID = COALESCE(@StatusID, StatusID)
	ORDER BY Name
  END

